/****** Object:  UserDefinedFunction [dbo].[RestaurantesBuscar]    Script Date: 12/28/2013 18:44:34 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RestaurantesBuscar]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[RestaurantesBuscar]
GO



CREATE FUNCTION [dbo].[RestaurantesBuscar](
	@ColumnaParaOrdenar varchar(100) = null,
	@PalabrasABuscar varchar(100) = null,
	@Rating int = null
	
)


RETURNS TABLE
AS

RETURN 

	(SELECT 
        ROW_NUMBER() OVER (ORDER BY         
        (CASE UPPER(@ColumnaParaOrdenar)
			WHEN 'NOMBRE ASC' THEN R.Nombre
			WHEN 'RATING ASC' THEN RIGHT('0000000000' + CAST(ISNULL(R.Rating, 0) AS VARCHAR(50)), 10)
			WHEN 'TIEMPOESPERADO ASC' THEN RIGHT('0000000000' + CAST(ISNULL(R.TiempoEstimadoMinimoId, 0) AS VARCHAR(50)), 10)
        END) ASC,
        (CASE @ColumnaParaOrdenar
			WHEN 'NOMBRE DESC' THEN R.Nombre
			WHEN 'RATING DESC' THEN RIGHT('0000000000' + CAST(ISNULL(R.Rating, 0) AS VARCHAR(50)), 10)
			WHEN 'TIEMPOESPERADO DESC' THEN RIGHT('0000000000' + CAST(ISNULL(R.TiempoEstimadoMinimoId, 0) AS VARCHAR(50)), 10)
        END) DESC   
    )  as Fila,
    R.restauranteId,
    R.Nombre,
    R.Descripcion,
    R.Rating,
	TE.Descripcion TiempoEstimado
	
	FROM restaurante R
		LEFT JOIN TiempoEstimado TE ON R.TiempoEstimadoMinimoId = TE.TiempoEstimadoId

    WHERE
		(@Rating IS NULL OR @Rating = R.Rating)
		
		AND (@PalabrasABuscar IS NULL OR @PalabrasABuscar = '' OR R.Nombre LIKE '%' + @PalabrasABuscar + '%'
																OR R.Descripcion LIKE '%' + @PalabrasABuscar + '%')
)
GO


